#!/use/bin/python
# coding=utf-8
# 记录 订阅服务的设备到redis上 无需执行一次
#KEY
# V2为：无需执行一次 V1为：需要执行一次
# LOG_DEVICE_ACTIVE_V2_ZJKJ_20221030 为20221030日早间服务当前总订阅量
# LOG_DEVICE_NEW_ACTIVE_V2_ZJKJ_20221030 为20221030日早间服务当天纯新增订阅量 LOG_DEVICE_REDUCE_ACTIVE_V2_ZJKJ_20221030  流失设备量
# LOG_DEVICE_ACTIVE_V2_ONCE7_ZJKJ 截止7日前曾经订阅过早间服务的设备激活id LOG_DEVICE_ACTIVE_V2_RETENTION7_ZJKJ  从新增后7天仍然订阅的设备
# LOG_DEVICE_ACTIVE_V2_ONCE_ALL 曾经订阅过生活助手的设备激活id
import redis
import datetime
import pymysql

from dbutils.pooled_db import PooledDB

# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.150.109', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur


# redis
def getReidsConnection():
    redis_info = {
        "host": "172.20.151.90",
        "password": "Coocaa2022",
        "port": 6379,
        "db": 8
    }

    r = redis.Redis(**redis_info, decode_responses=True)
    return r


# 记录 各服务每天订阅的设备 到redis 中
#serId 服务
def saveActive(date,serId):
    try:
        sqlStr = getSqlStr(serId)
        indexArray = serIdToStateIndex(serId)
        selectNumSql = f"select t.active_id from life_assistant_devices t where (switches_state1 & (1 << ({indexArray[0]} - 1)))<>0 and (switches_state1 & (1 << ({indexArray[1]} - 1)))<>0 {sqlStr}";

        # 获取当日早间服务订阅数量
        conn, cur = getConfConnection()
        r = getReidsConnection()
        cur.execute(selectNumSql)
        resultNum = cur.fetchall()
        key = f"LOG_DEVICE_ACTIVE_{serId.upper()}_"+date
        for result in resultNum:
            r.sadd(key, result[0])
        # 设置有效期
        r.expire(key, expire_time)
        serName = serIdToName(serId.upper())
        print(f'{serName}:当前服务订阅量为{len(resultNum)}')
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

#记录助眠服务订阅的设备到redis中
def saveZmgnActive(date):
    try:
        selectNumSql = f"select t.active_id from life_assistant_devices t where (switches_state1 & (1 << (16 - 1)))<>0 ";

        # 获取当日助眠功能订阅设备
        conn, cur = getConfConnection()
        r = getReidsConnection()
        cur.execute(selectNumSql)
        resultNum = cur.fetchall()
        key = f"LOG_DEVICE_ACTIVE_ZMGN_"+date
        for result in resultNum:
            r.sadd(key, result[0])
        # 设置有效期
        r.expire(key, expire_time)
        print(f'助眠功能:当前服务订阅量为{len(resultNum)}')

        #获取当日助眠功能执行的设备
        selectExecuteSql = f"select t.active_id from log_common_service_{yyMMddDate} t where t.`key` = 'saEnterPage'";
        conn, cur = getDataConnection()
        cur.execute(selectExecuteSql)
        resultNum = cur.fetchall()
        key = f"LOG_DEVICE_ACTIVE_ZMGN_EXECUTE_" + date
        for result in resultNum:
            r.sadd(key, result[0])
        # 设置有效期
        #r.expire(key, expire_time)
        print(f'助眠功能:当前服务执行量为{len(resultNum)}')
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


def getSqlStr(serId):
    #v1 表示执行一次 v2表示未执行一次
    if serId =='JKZX':
        sqlStr = f"and (switches_state1 & (1 << ({13} - 1)))<>0 "
    elif serId =='YJYL':
        sqlStr = f"and (switches_state1 & (1 << ({12} - 1)))<>0 "
    elif serId =='ZJTX':
        sqlStr = f"and (switches_state1 & (1 << ({14} - 1)))<>0 "
    else:
        sqlStr = ""
    return sqlStr

#根据serId 获取开关位置
#返回: 第一位服务开关 第二位UI开关 第三位 执行标志位
def serIdToStateIndex(serId):
    indexArray = []
    if serId =='ZJKJ':
        indexArray.append(2)
        indexArray.append(1)
        indexArray.append(11)
    elif serId =='JKZX':
        indexArray.append(6)
        indexArray.append(5)
        indexArray.append(13)
    elif serId =='YJYL':
        indexArray.append(4)
        indexArray.append(3)
        indexArray.append(12)
    elif serId =='ZJTX':
        indexArray.append(8)
        indexArray.append(7)
        indexArray.append(14)
    elif serId =='SJTX':
        indexArray.append(10)
        indexArray.append(9)
        indexArray.append(15)
    return indexArray

# 记录 各服务每天新增订阅的设备 到redis 中
#serId 服务 executeFlag:是否执行一次 1是 0否
def saveNewActive(toDay,beforeDay,serId):
    r = getReidsConnection()
    todayKey = f"LOG_DEVICE_ACTIVE_{serId.upper()}_{toDay}"
    beforeKey = f"LOG_DEVICE_ACTIVE_{serId.upper()}_{beforeDay}"
    newKey = f"LOG_DEVICE_NEW_ACTIVE_{serId.upper()}_{toDay}"
    reducekey = f"LOG_DEVICE_REDUCE_ACTIVE_{serId.upper()}_{toDay}"
    # 取 在当日订阅设备 但不在昨日的订阅设备中的 设备 作为当日的新增设备 存入redis
    r.sdiffstore(newKey, todayKey,beforeKey)
    # 取 在昨日订阅设备 但不在今日的订阅设备中的 设备 作为当日的流失设备 存入redis
    r.sdiffstore(reducekey, beforeKey, todayKey)
    todayNum = r.scard(todayKey)
    beforeNum = r.scard(beforeKey)
    newNum = r.scard(newKey)
    reduceNum = r.scard(reducekey)
    serName = serIdToName(serId.upper())
    # 设置有效期
    r.expire(newKey, expire_time)
    r.expire(reducekey, expire_time)
    logDetail = f'{serName}-昨日订阅设备量{beforeNum},今日订阅设备量{todayNum},纯新增订阅设备{newNum},流失订阅设备量{reduceNum}'
    print(logDetail)
    saveLog(logDetail, date)

#获取助眠功能最近7天新增的设备数
def saveZmgnNew7dayAvtive(toDay):
    r = getReidsConnection()
    new7DayKey = f"LOG_DEVICE_NEW_ACTIVE_7DAY_ZMGN"
    r.delete(new7DayKey)
    for i in range(1, 8):
        beforeDay = strToTime(toDay, -i)
        dayNewKey = f"LOG_DEVICE_NEW_ACTIVE_ZMGN_{beforeDay}"
        r.sunionstore(new7DayKey, dayNewKey, new7DayKey)
    new7DayKeyNum = r.scard(new7DayKey)
    logDetail = f'助眠功能:最近新增的设备数为{new7DayKeyNum}'
    print(logDetail)

# 1.记录 截止7天前曾经订阅过的设备
# 2.记录 从新增 后7天 仍然订阅的设备
# toDay 日期 serId: 服务id
def saveOnceAndRetentionActive(toDay,serId):
    r = getReidsConnection()
    #前7天日期
    before7d = strToTime(toDay, -7)
    before7dKey = f"LOG_DEVICE_ACTIVE_{serId.upper()}_{before7d}"
    day7KeyNum = r.scard(before7dKey)
    onceKey = f'LOG_DEVICE_ACTIVE_ONCE7_{serId.upper()}'
    onceKeyNum = r.scard(onceKey)
    #将7天前的订阅激活id 加入到 "截止7天前曾经订阅过的设备"中
    r.sunionstore(onceKey, before7dKey, onceKey)
    newOnceNum = r.scard(onceKey)
    # 获取符合7天留存的设备量
    day7NewKey = f"LOG_DEVICE_NEW_ACTIVE_{serId.upper()}_{before7d}"
    day7NewKeyNum = r.scard(day7NewKey)
    toDaykey = f"LOG_DEVICE_ACTIVE_{serId.upper()}_{toDay}"
    toDaykeyNum = r.scard(toDaykey)
    retentionKey = f'LOG_DEVICE_ACTIVE_RETENTION7_{serId.upper()}'
    retentionKeyNum = r.scard(retentionKey)
    newTempKey = f'LOG_DEVICE_ACTIVE_TEMP_{serId.upper()}'
    # N日且N+7日服务处于打开的设备
    r.sinterstore(newTempKey, toDaykey, day7NewKey)
    newTempNum = r.scard(newTempKey)
    r.sunionstore(retentionKey, newTempKey, retentionKey)
    newRetentionNum = r.scard(retentionKey)
    serName = serIdToName(serId.upper())
    # 设置有效期
    logDetail = f'{serName}:截止7天前累计曾经订阅数为{newOnceNum},当天新增的7日留存量为{newTempNum},总7日留存量为为{newRetentionNum}'
    print(logDetail)
    saveLog(logDetail, date)


# toDay 日期
#只记录助眠功能的
def saveZmgnOnceAndRetentionActive(toDay):
    r = getReidsConnection()
    #前7天日期
    before7dKey = f"LOG_DEVICE_ACTIVE_ZMGN_{toDay}"
    day7KeyNum = r.scard(before7dKey)
    onceKey = f'LOG_DEVICE_ACTIVE_ONCE7_ZMGN'
    onceKeyNum = r.scard(onceKey)
    #将7天前的订阅激活id 加入到 "截止7天前曾经订阅过的设备"中
    r.sunionstore(onceKey, before7dKey, onceKey)
    newOnceNum = r.scard(onceKey)
    # 获取符合7天留存的设备量
    day7NewKey = f"LOG_DEVICE_NEW_ACTIVE_7DAY_ZMGN"
    day7NewKeyNum = r.scard(day7NewKey)
    toDaykey = f"LOG_DEVICE_ACTIVE_ZMGN_EXECUTE_{toDay}"
    toDaykeyNum = r.scard(toDaykey)
    retentionKey = f'LOG_DEVICE_ACTIVE_RETENTION7_ZMGN'
    retentionKeyNum = r.scard(retentionKey)
    newTempKey = f'LOG_DEVICE_ACTIVE_TEMP_ZMGN'
    # N日且N+7日服务处于打开的设备
    r.sinterstore(newTempKey, toDaykey, day7NewKey)
    newTempNum = r.scard(newTempKey)
    r.sunionstore(retentionKey, newTempKey, retentionKey)
    newRetentionNum = r.scard(retentionKey)
    # 设置有效期
    logDetail = f'助眠功能:截止7天前累计曾经订阅数为{newOnceNum},当日执行助眠功能设备量为{toDaykeyNum},当天新增的7日留存量为{newTempNum},总7日留存量为为{newRetentionNum}'
    print(logDetail)
    saveLog(logDetail, date)

#截止当天所有累计的订阅设备量
def saveOnceActive(toDay,serId):
    r = getReidsConnection()
    # 当天订阅设备
    dayKey = f"LOG_DEVICE_ACTIVE_{serId.upper()}_{toDay}"
    dayKeyNum = r.scard(dayKey)
    # 累计订阅设备
    onceKey = f'LOG_DEVICE_ACTIVE_ONCE_{serId.upper()}'
    onceKeyNum = r.scard(onceKey)
    # 将当天的订阅激活id 加入到 "累计订阅设备"中
    r.sunionstore(onceKey, dayKey, onceKey)
    newOnceNum = r.scard(onceKey)
    serName = serIdToName(serId.upper())
    logDetail = f'{serName}:今天总订阅数为{dayKeyNum},昨天累计设备数量{onceKeyNum},取交集后总累计曾经订阅数量为{newOnceNum},'
    print(logDetail)
    saveLog(logDetail, date)


#根据serId 获取服务中文名
def serIdToName(serId):
    if serId =='ZJKJ':
        serName = "早间开机"
    elif serId =='JKZX':
        serName = "健康作息"
    elif serId =='YJYL':
        serName = "夜间音量"
    elif serId =='ZJTX':
        serName = "追剧提醒"
    elif serId =='SJTX':
        serName = "睡觉提醒"
    elif serId =='ZMGN':
        serName = "助眠功能"
    else:
        serName = "生活助手总体"
    return serName

#将指定字符串 dateStr ,偏移 offsetInt 天后再输出字符串
#如 20220805 偏移7天 即增加7天后 输出 20220812
def strToTime(dateStr,offsetInt):
    t = datetime.datetime.strptime(dateStr, "%Y%m%d")
    #print(t)
    offset = datetime.timedelta(days=offsetInt)
    re_date = (t+offset).strftime('%Y%m%d')
    #print(re_date)
    return  re_date

# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y%m%d')
    return re_date

#如 2022年12月13号 获取 221213 作为分表后缀
def getdateyyMMdd(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y%m%d')
    re_date = re_date[2:]
    return re_date

def saveLog(detail,ref_date):
    try:
        conn, cur = getDataConnection()
        jobId = 41
        jobName = '	日志-将每日订阅的设备激活id存入redis'
        tableName = ''
        # 插入日志表
        saveLogSql = f"""insert into log_xxljob_analysis (job_id,job_name,detail,table_name,ref_date) 
                                value({jobId},'{jobName}','{detail}','{tableName}','{ref_date}')"""
        cur.execute(saveLogSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

def saveActiveMain():
    saveActive(date, 'ZJKJ')
    saveActive(date, 'JKZX')
    saveActive(date, 'YJYL')
    saveActive(date, 'ZJTX')
    saveActive(date, 'SJTX')
    saveZmgnActive(date)

def saveNewActiveMain():
    saveNewActive(date, beforeDate, 'ZJKJ')
    saveNewActive(date, beforeDate, 'JKZX')
    #saveNewActive(date, beforeDate, 'YJYL')
    saveNewActive(date, beforeDate, 'ZJTX')
    saveNewActive(date, beforeDate, 'SJTX')
    saveNewActive(date, beforeDate, 'ZMGN')

def saveOnceAndRetentionActiveMain():
    saveOnceAndRetentionActive(date, 'ZJKJ')
    saveOnceAndRetentionActive(date, 'YJYL')
    saveOnceAndRetentionActive(date, 'JKZX')
    saveOnceAndRetentionActive(date, 'ZJTX')
    saveOnceAndRetentionActive(date, 'SJTX')
    #saveOnceAndRetentionActive(date, 'ZMGN')
    saveZmgnOnceAndRetentionActive(date)

def saveOnceActiveMain():
    saveOnceActive(date, 'ZJKJ')
    saveOnceActive(date, 'SJTX')

if __name__ == '__main__':
    # key 的有效期为8天
    expire_time = 60*60*24*8
    expire_time60 = 60 * 60 * 24 * 60
    d = 1
    date = getdate(d)
    beforeDate = getdate(d+1)
    yyMMddDate = getdateyyMMdd(d)
    print (f"{date} 日期,记录 订阅服务的设备到redis上")
    #设置每天总订阅量
    saveActiveMain()
    #设置每天新增的订阅量
    saveNewActiveMain()
    #设置助眠功能最近7天新增的设备数
    saveZmgnNew7dayAvtive(date)
    #设置7日曾经总订阅量及7日历史留存量
    saveOnceAndRetentionActiveMain()
    #获取截止今天累计订阅设备量
    saveOnceActiveMain()
    saveZmgnActive(date)



